//
//  ViewController.m
//  SQLiteDemo
//
//  Created by UniHan_iMac on 17/4/1.
//  Copyright © 2017年 UniHan_iMac. All rights reserved.
//

#import "ViewController.h"
#import <sqlite3.h>

@interface ViewController ()
{
    sqlite3 *_db;
    NSString *_filePath;
    __weak IBOutlet UITextField *_nameTF;
    __weak IBOutlet UITextField *_ageTF;
}
@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // 文件路径
    _filePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0] stringByAppendingPathComponent:@"students.sqlite"];
    
    // 打开数据库
    // [filePath UTF8String] 将oc字符串转化为c的字符串
    int isOpen = sqlite3_open([_filePath UTF8String], &_db);
    if(isOpen == SQLITE_OK)
    {
        // 创建表
        NSString *studentTable = @"CREATE TABLE IF NOT EXISTS student(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,name TEXT,age INTEGER)";
        int isCreate = sqlite3_exec(_db, [studentTable UTF8String], NULL, NULL, nil);
        if(isCreate == SQLITE_OK)
        {
        }else{}
    }else{}
    sqlite3_close(_db);
}

// 插入数据
- (IBAction)Insert:(id)sender {
    if(sqlite3_open([_filePath UTF8String], &_db) == SQLITE_OK)
    {
//        NSString *insertString = @"INSERT INTO student(name,age) VALUES('蔷薇',23)";
        NSString *insertString = [NSString stringWithFormat:@"INSERT INTO student(name,age) VALUES('%@',%d)",_nameTF.text,[_ageTF.text intValue]];
        char *error = nil;
        if(sqlite3_exec(_db, [insertString UTF8String], NULL, NULL, &error) == SQLITE_OK)
        {
        }else
        {
            NSLog(@"插入数据失败:%s",error);
        }
    }else{}
    sqlite3_close(_db);
}

// 修改数据
- (IBAction)Update:(id)sender {
    if(sqlite3_open([_filePath UTF8String], &_db) == SQLITE_OK)
    {
        NSString *updateString = @"UPDATE student SET age=24 WHERE name LIKE '蔷薇'";
        if(sqlite3_exec(_db, [updateString UTF8String], NULL, NULL, nil) == SQLITE_OK)
        {
        }else{}
    }else{}
    sqlite3_close(_db);
}

// 删除数据
- (IBAction)Delete:(id)sender {
    if(sqlite3_open([_filePath UTF8String], &_db) == SQLITE_OK)
    {
        // %:通配多个字符; _:通配一个字符
        NSString *deleteString = @"DELETE FROM student WHERE name LIKE '蔷%'";
        if(sqlite3_exec(_db, [deleteString UTF8String], NULL, NULL, nil) == SQLITE_OK)
        {
        }else{}
    }else{}
    sqlite3_close(_db);
}

// 查询数据
- (IBAction)Select:(id)sender {
    if(sqlite3_open([_filePath UTF8String], &_db) == SQLITE_OK)
    {
        NSString *selectString = @"SELECT name,age FROM student";
        // sqlite3_stmt: 缓冲区
        sqlite3_stmt *stmt = nil;
        if(sqlite3_prepare(_db, [selectString UTF8String], -1, &stmt, nil) == SQLITE_OK)
        {
            // sqlite3_step: 表示单步执行查询语句
            // SQLITE_ROW  : 表示下一条记录准备完毕，可以继续查询
            while(sqlite3_step(stmt) == SQLITE_ROW)
            {
                // 获取查询结果
                const char *name = (const char *)sqlite3_column_text(stmt, 0);
                // 将c的字符串转化为oc的字符串
                [NSString stringWithUTF8String:name];
                sqlite3_column_int(stmt, 1);
            }
        }else{}
    }else{}
    sqlite3_close(_db);
}


@end
